package com.android.justcook.sqlite;

import java.util.Hashtable;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class RecipesDataSource {
	private final String TAG = "RecipesDataSource";
	// Database fields
	private SQLiteDatabase database;
	private MySQLiteHelper dbHelper;
	private String[] allColumns = { MySQLiteHelper.COLUMN_ID,
			MySQLiteHelper.COLUMN_NAME, MySQLiteHelper.COLUMN_POINTS,
			MySQLiteHelper.COLUMN_INGREDIENTS, MySQLiteHelper.COLUMN_METHOD,
			MySQLiteHelper.COLUMN_DONE, MySQLiteHelper.COLUMN_FAVOURITE,
			MySQLiteHelper.COLUMN_PICTURE, MySQLiteHelper.COLUMN_EXPIRE,
			MySQLiteHelper.COLUMN_EXPIRE_TS };

	public RecipesDataSource(Context context) {
		dbHelper = new MySQLiteHelper(context);
	}

	public void open() throws SQLException {
		database = dbHelper.getWritableDatabase();
	}

	public void close() {
		dbHelper.close();
	}

	public Recipe insertRecipe(Recipe recipe) {
		ContentValues values = new ContentValues();

		values.put(MySQLiteHelper.COLUMN_ID, recipe.getId());
		values.put(MySQLiteHelper.COLUMN_NAME, recipe.getName());
		values.put(MySQLiteHelper.COLUMN_POINTS, recipe.getPoints());
		values.put(MySQLiteHelper.COLUMN_INGREDIENTS, recipe.getIngredients());
		values.put(MySQLiteHelper.COLUMN_METHOD, recipe.getIngredients());
		values.put(MySQLiteHelper.COLUMN_METHOD, recipe.getMethod());
		values.put(MySQLiteHelper.COLUMN_DONE, recipe.getDone());
		values.put(MySQLiteHelper.COLUMN_FAVOURITE, recipe.getPicture());
		values.put(MySQLiteHelper.COLUMN_EXPIRE, recipe.getExpire());
		values.put(MySQLiteHelper.COLUMN_EXPIRE_TS,
				recipe.getExpire_time_stamp());
		long insertId = database.insert(MySQLiteHelper.TABLE_RECIPES, null,
				values);

		Cursor cursor = database.query(MySQLiteHelper.TABLE_RECIPES,
				allColumns, MySQLiteHelper.COLUMN_ID + " = '" + recipe.getId()
						+ "' ", null, null, null, null);

		cursor.moveToFirst();
		Recipe newRecipe = cursorToRecipe(cursor);
		cursor.close();
		return newRecipe;
	}

	public void insertRelationship(Relationship relationship) {
		ContentValues values = new ContentValues();

		values.put(MySQLiteHelper.CATEGORIES_CID, relationship.getCategoryId());
		values.put(MySQLiteHelper.CATEGORIES_RID, relationship.getRecipeId());
		long insertId = database.insert(MySQLiteHelper.TABLE_CATEGORIES, null,
				values);
	}

	public void deleteRecipe(Recipe recipe) {
		int id = recipe.getId();
		System.out.println("Recipe deleted with id: " + id);
		database.delete(MySQLiteHelper.TABLE_RECIPES, MySQLiteHelper.COLUMN_ID
				+ " = " + id, null);
	}

	public Hashtable<Integer, Hashtable<String,String>> getAllCategoryRecepies(int searchCategoryID) {
			String joinQuery = "SELECT * FROM " + MySQLiteHelper.TABLE_RECIPES + " AS recipes and "
						+ MySQLiteHelper.TABLE_CATEGORIES + " AS categories WHERE categories.category_id = "
						+ searchCategoryID + " and categories.recipe_id = recipe.id";
			
			Cursor cursor = database.rawQuery(joinQuery,null);
			
			Hashtable<Integer, Hashtable<String,String>> ht = new Hashtable<Integer, Hashtable<String,String>>();
			
			if (cursor != null)
			{
				cursor.moveToFirst();
				
				while(!cursor.isAfterLast())
				{
					Hashtable<String,String> newRecipe = new Hashtable<String,String>();
					newRecipe.put(cursor.getColumnName(0), cursor.getString(0));
					newRecipe.put(cursor.getColumnName(1), cursor.getString(1));
					newRecipe.put(cursor.getColumnName(2), cursor.getString(2));
					newRecipe.put(cursor.getColumnName(3), cursor.getString(3));
					newRecipe.put(cursor.getColumnName(4), cursor.getString(4));
					newRecipe.put(cursor.getColumnName(5), cursor.getString(5));
					newRecipe.put(cursor.getColumnName(6), cursor.getString(6));
					newRecipe.put(cursor.getColumnName(7), cursor.getString(7));
					newRecipe.put(cursor.getColumnName(8), cursor.getString(8));
				
					ht.put(cursor.getInt(0), newRecipe);
				
					cursor.moveToNext();
				}
			}	
			return ht;
		}

	public void trunkateDatabase() {
		database.execSQL("DELETE FROM " + MySQLiteHelper.TABLE_RECIPES);
		database.execSQL("VACUUM");
	}

	// get recipes count
	public int getRecipesCount() {
		String countQuery = "SELECT * FROM " + MySQLiteHelper.TABLE_RECIPES;
		Cursor cursor = database.rawQuery(countQuery, null);
		int recipesCount = cursor.getCount();
		cursor.close();

		// return count
		return recipesCount;
	}

	private Recipe cursorToRecipe(Cursor cursor) {
		Recipe recipe = new Recipe(cursor.getInt(0), cursor.getString(1),
				cursor.getInt(2), cursor.getString(3), cursor.getString(4),
				cursor.getInt(5), cursor.getInt(6), cursor.getString(7),
				cursor.getString(8), cursor.getInt(9));

		return recipe;
	}
}
